﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace CustomerServiceCenter.Helper
{
    public class ExcelUntils
    {
        private static IWorkbook workBook = null;
        private static Stream fs = null;
        private static ISheet sheet = null;
        private static string[] headInfo = null;
        /// <summary>
        /// 从Excel中获取数据对象
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <param name="excelToField">Excel中的列名和类字段名的映射,key是Excel表头名称,Value是类的属性名</param>
        /// <param name="type">类的类型</param>
        /// <param name="fromData">数据行从第几行开始的,默认是从第一行开始</param>
        /// <returns></returns>
        public static List<T> GetObjectFromExcel<T>(string filePath,long CustomerID,long ProjectID, Dictionary<string, string> excelToField = null, int fromData = 1)
        {
            fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            GetExcelInfo(fromData);
            List<T> list =  SetObject<T>(excelToField, fromData,CustomerID,ProjectID);
            CloseStream();
            return list;
        }

        public static List<T> GetObjectFromExcel<T>(Stream stream,long CustomerID,long ProjectID, Dictionary<string, string> excelToField = null, int fromData = 1)
        {
            fs = stream;
            GetExcelInfo(fromData);
            List<T> list = SetObject<T>(excelToField, fromData,CustomerID, ProjectID);
            CloseStream();
            return list;
        }

        /// <summary>
        /// 将获取的数据和类进行匹配产生对象
        /// </summary>
        /// <typeparam name="T">待产生的集合的类</typeparam>
        /// <param name="mapping">Excel的列名和类的类字段名映射</param>
        /// <param name="type">类的类型</param>
        /// <param name="fromData"></param>
        /// <returns></returns>
        private static List<T> SetObject<T>(Dictionary<string, string> mapping, int fromData,long CustomerID,long ProjectID)
        {
            List<T> list = new List<T>();
            Type type = typeof(T);
            for (int row = fromData; row < sheet.LastRowNum; row++)
            {
                dynamic obj = Activator.CreateInstance(type);
                //获取当前行
                IRow currentRow = sheet.GetRow(row);
                for (int cellNum = 0; cellNum < currentRow.LastCellNum; cellNum++)
                {
                    //获取当前单元格
                    ICell cell = currentRow.GetCell(cellNum);
                    string cellName = headInfo[cellNum];
                    string fieldName = null;
                    if (mapping != null)
                    {
                        fieldName = mapping[cellName];
                    }
                    else
                    {
                        fieldName = cellName;
                    }

                    PropertyInfo property = typeof(T).GetProperty(fieldName);
                    if (property != null&&cell!=null)
                    {
                        if (property.PropertyType.Equals(typeof(String)))
                        {
                            string value = null;
                            cell.SetCellType(CellType.String);
                            value = cell.StringCellValue;
                            property.SetValue(obj, value,null);
                        }
                        else if (property.PropertyType.Equals(typeof(DateTime))||property.PropertyType.Equals(typeof(Nullable<DateTime>)))
                        {
                            DateTime time = DateTime.MinValue;
                            //if (cell!=null)
                            //{
                                time = cell.DateCellValue;
                            //}
                            property.SetValue(obj, time, null);
                        }
                        else if (property.PropertyType.Equals(typeof(Boolean)))
                        {
                            property.SetValue(obj, cell.BooleanCellValue,null);
                        }
                        else
                        {
                            dynamic value = null;
                            Type propertyType = property.PropertyType;
                            if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                            {
                                Type NullType = Nullable.GetUnderlyingType(propertyType);
                                value = Convert.ChangeType(cell.NumericCellValue, NullType);
                            }
                            else
                            {
                                value = Convert.ChangeType(cell.NumericCellValue, propertyType);
                            }
                            property.SetValue(obj, value,null);
                        }
                    }
                    property.SetValue("CustomerID", CustomerID, null);
                    property.SetValue("ProjectID", ProjectID, null);
                    property.SetValue("IsFromWeChat", 2, null);
                }

                
                list.Add(obj);
            }
            return list;
        }


        /// <summary>
        /// 设置一些EXCEL相关的内容
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="fromData"></param>
        private static void GetExcelInfo(int fromData)
        {
            try
            {
                workBook = WorkbookFactory.Create(fs);
                sheet = workBook.GetSheetAt(0);
                //获取第一行数据
                IRow firstRow = sheet.GetRow(fromData - 1);
                headInfo = GetHeadInfo(firstRow);
            }
            catch (Exception)
            {
                CloseStream();
                throw;
            }
        }

        /// <summary>
        /// 获取第一行的信息,也就是列名
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        private static string[] GetHeadInfo(IRow row)
        {
            int cellCount = row.LastCellNum;
            string[] obj = new string[cellCount];
            for (int i = row.FirstCellNum; i < row.LastCellNum; i++)
            {
                ICell cell = row.GetCell(i);
                if (cell != null)
                {
                    string cellValue = cell.StringCellValue;
                    if (cellValue != null)
                    {
                        obj[i] = cellValue;
                    }
                }
            }
            return obj;
        }
        /// <summary>
        /// 关闭文件流
        /// </summary>
        private static void CloseStream()
        {
            if (fs!=null)
            {
                fs.Close();
                fs = null;
            }
        }

        /// <summary>
        /// 导出Excel表格
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static bool DataTableToExcel(DataTable dt, string fileName)
        {
            bool result = false;
            IWorkbook workbook = null;
            FileStream fs = null;
            IRow row = null;
            ISheet sheet = null;
            ICell cell = null;
            try
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    workbook = new HSSFWorkbook();
                    sheet = workbook.CreateSheet("Sheet0");//创建一个名称为Sheet0的表  
                    int rowCount = dt.Rows.Count;//行数  
                    int columnCount = dt.Columns.Count;//列数  

                    //设置列头  
                    row = sheet.CreateRow(0);//excel第一行设为列头  
                    for (int c = 0; c < columnCount; c++)
                    {
                        cell = row.CreateCell(c);
                        cell.SetCellValue(dt.Columns[c].ColumnName);
                    }

                    //设置每行每列的单元格,  
                    for (int i = 0; i < rowCount; i++)
                    {
                        row = sheet.CreateRow(i + 1);
                        for (int j = 0; j < columnCount; j++)
                        {
                            cell = row.CreateCell(j);//excel第二行开始写入数据  
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                        }
                    }
                    using (fs = File.OpenWrite(@fileName))
                    {
                        workbook.Write(fs);//向打开的这个xls文件中写入数据  
                        result = true;
                    }
                }
                return result;
            }
            catch (Exception)
            {
                CloseStream();
                return false;
            }
        }


        #region  反射List To DataTable
        ///  
        /// 将List集合类转换成DataTable 
        ///  
        /// 集合 
        ///  
        public static DataTable ListToDataTable<T>(List<T> list)
        {
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    //获取类型
                    Type colType = pi.PropertyType;
                    //当类型为Nullable<>时
                    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                    {

                        colType = colType.GetGenericArguments()[0];

                    }

                    result.Columns.Add(pi.Name, colType);
                }

                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(list[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }
        #endregion

    }
}
